-- grdlog_event is a template

-- Create current table, into which data should be inserted
CREATE OR REPLACE FUNCTION new_event_table() RETURNS void AS $$
       CREATE TABLE grdlog_event_current () INHERITS (grdlog_event); /* */
       CREATE RULE grdlog_event_insert AS ON INSERT TO grdlog_event
              DO INSTEAD
              INSERT INTO grdlog_event_current VALUES (NEW.*); /* */
$$ LANGUAGE SQL;

-- Replace current table with new blank, leaving num number tables
CREATE OR REPLACE FUNCTION rotate_event_table(num int) RETURNS void AS $$
DECLARE
	tables refcursor; /* */
	tables_count int; /* */
	item varchar; /* */
BEGIN
	-- current become YYYYMMDD
	DROP RULE grdlog_event_insert ON grdlog_event; /* */
	EXECUTE 'ALTER TABLE grdlog_event_current RENAME TO '
		|| 'grdlog_event_' || to_char(current_date, 'YYYYMMDD'); /*
	new current table */
	PERFORM new_event_table(); /*
	delete extra tables, leaving num */
	IF num > 0 THEN
	   tables_count := count(*) FROM pg_tables
	   		   WHERE tablename LIKE 'grdlog\_event\_________'; /* */
	   IF tables_count > num THEN
	      OPEN tables FOR SELECT tablename FROM pg_tables
	      	   WHERE tablename LIKE 'grdlog\_event\_________'
		   AND tablename != 'grdlog_event_current'
	      	   ORDER BY tablename DESC; /* */
	      MOVE ABSOLUTE num - 1 IN tables; /* */
	      LOOP
		FETCH tables INTO item; /* */
		IF NOT FOUND THEN EXIT; END IF; /* */
		EXECUTE 'DROP TABLE ' || item; /* */
	      END LOOP; /* */
	   END IF; /* */
	END IF; /* */
END
$$ LANGUAGE PLPGSQL;

SELECT new_event_table();
